using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace GeneralLibs
{
    public class GiaoDienFormDanhSachChungTu
    {
        public GiaoDienFormDanhSachChungTu()
        {
            _DataTable = new DataTable();
        }

        public void LoadFromDB()
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT A.*, B.LoaiChungTuID, B.TenHienThi, B.DoRong, B.DinhDang, B.HienThi, B.ThuTuHienThi \n" +
                                    "FROM CotChungTu A INNER JOIN GiaoDienFormDanhSachChungTu B ON A.ColumnID = B.ColumnID \n" +
                                                        "INNER JOIN LoaiChungTu C ON B.LoaiChungTuID = C.ID";
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                _DataTable.Clear();
                da.Fill(_DataTable);

                //lay hai bang LoaiChungTu va CotChungTu ra, neu co tp tich nao chua co trong ds thi insert vao db va _DataTable
                DataTable dtLoaiChungTu = new DataTable();
                cmd.CommandText = "SELECT ID FROM LoaiChungTu";
                da.SelectCommand = cmd;
                da.Fill(dtLoaiChungTu);

                DataTable dtCotChungTu = new DataTable();
                cmd.CommandText = "SELECT ColumnID, [Column Name] FROM CotChungTu";
                da.SelectCommand = cmd;
                da.Fill(dtCotChungTu);

                Guid loaiChungTuID; int columnID;
                foreach (DataRow drLoaiChungTu in dtLoaiChungTu.Rows)
                {
                    foreach (DataRow drCotChungTu in dtCotChungTu.Rows)
                    {
                        loaiChungTuID = (Guid)drLoaiChungTu["ID"];
                        
                        Type type = drCotChungTu["ColumnID"].GetType();
                        if(type.Name == "Int16")
                            columnID = (Int16)drCotChungTu["ColumnID"];
                        else
                            columnID = (int)drCotChungTu["ColumnID"];

                        if (_DataTable.Select(string.Format("LoaiChungTuID = '{0}' AND ColumnID = {1}", loaiChungTuID, columnID)).Length == 0)
                        {
                            DataRow newRow = _DataTable.NewRow();
                            newRow["LoaiChungTuID"] = loaiChungTuID;
                            newRow["ColumnID"] = columnID;
                            newRow["Column Name"] = drCotChungTu["Column Name"];
                            newRow["TenHienThi"] = drCotChungTu["Column Name"];
                            newRow["DoRong"] = 100;
                            newRow["DinhDang"] = "";
                            newRow["HienThi"] = 0;
                            newRow["ThuTuHienThi"] = 0;
                            _DataTable.Rows.Add(newRow);

                            cmd.CommandText = string.Format("INSERT INTO GiaoDienFormDanhSachChungTu(LoaiChungTuID, ColumnID, TenHienThi, DoRong, DinhDang, HienThi, ThuTuHienThi) \n" +
                                                           "VALUES('{0}', {1}, N'{2}', {3}, N'{4}', {5}, {6})",
                                                           newRow["LoaiChungTuID"],
                                                           newRow["ColumnID"],
                                                           newRow["TenHienThi"],
                                                           newRow["DoRong"],
                                                           newRow["DinhDang"],
                                                           0,
                                                           newRow["ThuTuHienThi"]
                                                           );
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }

        #region DataTable property
        private DataTable _DataTable;
        public DataTable DataTable
        {
            get
            {
                if (_DataTable == null || _DataTable.Rows.Count == 0) LoadFromDB();
                return _DataTable;
            }
            set
            {
                _DataTable = value;
            }
        }
        #endregion

        public DataView Filter(Guid loaiChungTuID)
        {
            try
            {
                if (_DataTable == null || _DataTable.Rows.Count == 0) LoadFromDB();
                DataView dv = new DataView(_DataTable);
                dv.RowFilter = "LoaiChungTuID = '" + loaiChungTuID.ToString() + "'";
                dv.Sort = "ColumnID";
                return dv;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void Update2DB(DataView dv)
        {
            if (dv.Table == _DataTable)
            {
                SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
                con.Open();
                try
                {
                    SqlCommand cmd = con.CreateCommand();
                    foreach (DataRowView dr in dv)
                    {
                        if (dr["TenHienThi"] is DBNull) dr["TenHienThi"] = (string)dr["Ten"];
                        if (dr["DoRong"] is DBNull) dr["DoRong"] = 100;
                        if (dr["DinhDang"] is DBNull) dr["DinhDang"] = "";
                        if (dr["HienThi"] is DBNull) dr["HienThi"] = false;
                        if (dr["ThuTuHienThi"] is DBNull) dr["ThuTuHienThi"] = 0;

                        cmd.CommandText = string.Format("UPDATE GiaoDienFormDanhSachChungTu SET \n" +
                                                                "TenHienThi = N'{2}', DoRong = {3}, DinhDang = N'{4}', HienThi = {5}, ThuTuHienThi = {6} \n" +
                                                        "WHERE LoaiChungTuID = '{0}' AND ColumnID = {1}",
                                                        dr["LoaiChungTuID"],
                                                        dr["ColumnID"],
                                                        dr["TenHienThi"],
                                                        dr["DoRong"],
                                                        dr["DinhDang"],
                                                        (bool)dr["HienThi"] == false ? 0 : 1,
                                                        dr["ThuTuHienThi"]);
                        cmd.ExecuteNonQuery();
                    }

                    //reload datatable
                    cmd.CommandText = "SELECT A.*, B.LoaiChungTuID, B.TenHienThi, B.DoRong, B.DinhDang, B.HienThi, B.ThuTuHienThi \n" +
                       "FROM CotChungTu A INNER JOIN GiaoDienFormDanhSachChungTu B ON A.ColumnID = B.ColumnID \n" +
                                           "INNER JOIN LoaiChungTu C ON B.LoaiChungTuID = C.ID";
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    _DataTable.Clear();
                    da.Fill(_DataTable);
                }
                catch (SqlException sqlEx)
                {
                    throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
                }
                catch (Exception ex)
                {
                    if (ex is CustomException) throw ex;
                    else
                    {
                        throw new Exception(ex.Message);
                    }
                }
                finally
                {
                    con.Close();
                    con = null;
                }
            }
        }
    }
}
